﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Runtime.InteropServices;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;

namespace EmbeddedLinkUpdater.Code.Documents
{
    public class ExcelDocument : Document
    {
        public ExcelDocument(string fileName) : base(fileName)
        {
        }

        public override void UpdateDocumentLinks(DataSet linksToReplace)
        {
            AnalyzeOrUpdateLinks(linksToReplace, true);
        }

        public override List<string> AnalyzeLinks()
        {
            AnalyzeOrUpdateLinks(null, false);

            return DocumentLinks;
        }

        private void AnalyzeOrUpdateLinks(DataSet linksToReplace, bool update)
        {
            Excel.Application excelApplication = null;

            try
            {
                excelApplication = new Excel.Application {Visible = false};
                excelApplication.AutomationSecurity = Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityForceDisable;

                if (FileInUse(FileName))
                    throw new ApplicationException("Failed to update. File in use.");

                var document = excelApplication.Workbooks.Open(FileName, ReadOnly: false);

                document.Activate();

                foreach (var range in document.Sheets.OfType<Excel.Worksheet>().Select(aSheet => aSheet.UsedRange))
                {
                    var links = range.Hyperlinks;
                    foreach(Excel.Hyperlink link in links)
                    {
                        if (update && linksToReplace != null)
                        {
                            var tableLinksToReplace = linksToReplace.Tables[0];
                            foreach (DataRow row in tableLinksToReplace.Rows)
                            {
                                if (link.Address != null)
                                    link.Address = link.Address.ReplaceIgnoreCase(row["OldLink"].ToString(), row["NewLink"].ToString());
                                    
                                if(!String.IsNullOrEmpty(link.TextToDisplay))
                                    link.TextToDisplay = link.TextToDisplay.ReplaceIgnoreCase(row["OldLink"].ToString(), row["NewLink"].ToString());
                            }
                        }
                        else
                        {
                            DocumentLinks.Add(link.Address);
                        }
                        Marshal.ReleaseComObject(link);
                    }
                }

                object workBookLinks = document.LinkSources(Excel.XlLink.xlExcelLinks);

                if((object)workBookLinks!=null)
                    foreach (object workBookLink in (Array)workBookLinks)
                        DocumentLinks.Add((string)workBookLink);

                document.Close(update);
                Marshal.ReleaseComObject(document);
            }
            catch (ApplicationException)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Failed to update document links", ex);
            }
            finally
            {
                if (excelApplication != null)
                {
                    excelApplication.Quit();
                    Marshal.ReleaseComObject(excelApplication);
                }

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
    }
}
